﻿using Nova_MOD;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Nova_DAL
{
    public class RFDal
    {
        DbHelper dbHelper = new DbHelper();

        /// <summary>
        /// 获取托盘组盘的物料信息
        /// </summary>
        /// <param name="trayId"></param>
        /// <returns></returns>
        public DataTable GetTrayMaterialInfor(string trayId)
        {
            string sqlStr = string.Format(@"SELECT ISNULL(TPT.StockNo,'') StockNo,TBS.PID,
                  TBM.Mat_Name,TPT.PackageNum Mat_Num,TBM.S01,TBM.Unit_Base,TBM.S05,TBM.TM01,TBM.TM03,TBM.S02,
                  TBI.Mat_Code,TBI.Sec_Mat_ID FROM dbo.TRelations TRP 
                  INNER JOIN dbo.TBase_TrayPos TBT ON TBT.TRAY_ID = TRP.TRAY_ID
                  INNER JOIN dbo.TRel_TrayPosContainer TRT ON TRT.RELATIONS_ID=TBT.RELATIONS_ID
                  INNER JOIN dbo.TBase_Storage TBS ON TBS.CONTAINERID=TRT.CONTAINERID
                  INNER JOIN dbo.TBase_Interface TBI ON TBI.SEC_MAT_ID=TBS.SEC_MAT_ID
                  INNER JOIN dbo.TBase_Material TBM ON TBM.MAT_CODE=TBI.MAT_CODE
                  LEFT JOIN dbo.TPackage_tail TPT  ON  TPT.TBStorage_PID=TBS.PID
                  INNER JOIN dbo.TAddStorage_List TAL ON TPT.StockNo = TAL.StockNo  AND TAL.Sec_Mat_ID = TBI.Sec_Mat_ID
                  WHERE  TRP.Tray_ID= @trayId  ORDER BY TPT.StockNo");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@trayId", DbType.String, trayId);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 获取托盘组盘的特殊物品物料信息
        /// </summary>
        /// <param name="trayId"></param>
        /// <returns></returns>
        public DataTable GetTraySpecialMaterialInfor(string trayId)
        {
            string sqlStr = string.Format(@"SELECT TRP.Tray_ID,TBM.Mat_Name,TBS.Mat_Num ,TBM.S01,TBM.S05, TBM.Unit_Base,
                  TMG.MaterialGroupType,TMG.MaterialGroupName,
                  TBI.Mat_Code,TBI.Sec_Mat_ID FROM dbo.TRelations TRP 
                  INNER JOIN dbo.TBase_TrayPos TBT ON TBT.TRAY_ID = TRP.TRAY_ID
                  INNER JOIN dbo.TRel_TrayPosContainer TRT ON TRT.RELATIONS_ID=TBT.RELATIONS_ID
                  INNER JOIN dbo.TBase_Storage TBS ON TBS.CONTAINERID=TRT.CONTAINERID
                  INNER JOIN dbo.TBase_Interface TBI ON TBI.SEC_MAT_ID=TBS.SEC_MAT_ID
                  INNER JOIN dbo.TBase_Material TBM ON TBM.MAT_CODE=TBI.MAT_CODE
				  INNER JOIN dbo.TMaterialGroupInfo TMG ON TBM.GroupType = TMG.MaterialGroupType
                  WHERE  TRP.Tray_ID= @trayId");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@trayId", DbType.String, trayId);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 获取单据物料信息
        /// </summary>
        /// <param name="StockNo"></param>
        /// <returns></returns>
        public DataTable DataListMaterial(string stockNo)
        {
            string sqlStr = @" SELECT TAL.Mat_Code,TBM.Mat_Name,TBM.S05,TBM.S01,TAL.Sec_Mat_ID,TAL.TS01 FROM TBill_AddStorage TBA 
                            LEFT JOIN TAddStorage_List TAL ON TAL.StockNo = TBA.StockNo
                            LEFT JOIN TBase_Material TBM ON TAL.Mat_Code=TBM.Mat_Code 
                            WHERE TBA.Bill_Status = 0 AND TBA.StockNo=@StockNo ORDER BY TAL.CDT DESC";  //  WHERE TBA.Bill_Status = 0 AND TBA.StockNo=@StockNo ORDER BY TAL.CDT DESC
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@StockNo", DbType.String, stockNo);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 获取入库单据上物料剩余数量
        /// </summary>
        /// <param name="StockNo"></param>
        /// <param name="MatCode"></param>
        /// <returns></returns>
        public int GetMaterialReNum(string stockNo, string matCode)
        {
            string sqlStr = @"SELECT(Num - Act_Num) as ReNum   FROM TBill_AddStorage TBA
                            LEFT JOIN TAddStorage_List TAL ON TAL.StockNo = TBA.StockNo
                            WHERE TBA.StockNo = @StockNo and Mat_Code =@Mat_Code";
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@StockNo", DbType.String, stockNo);
            dbHelper.AddInParameter(cmd, "@Mat_Code", DbType.String, matCode);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            int num = 0;
            if (dt.Rows.Count > 0)
                num = Convert.ToInt32(dt.Rows[0]["ReNum"]);
            return num;

        }

        /// <summary>
        /// 获取入库单据上物料的数量
        /// </summary>
        /// <param name="stockNo"></param>
        /// <param name="matCode"></param>
        /// <param name="serialNumber"></param>
        /// <returns></returns>
        public DataTable GetAddStorageMaterialInfo(string stockNo, int sec_Mat_Id,string pid)
        {
            string sqlStr = @"SELECT TAD.Mat_Code,TAD.Sec_Mat_ID,TAD.TS01,TBM.TM01,TBM.TM03,TBM.S02,(TAD.Num - TAD.Act_Num) SurplusNum,
                            ISNULL(TPT.PackageNum,0) PackageNum FROM  dbo.TAddStorage_List TAD 
                            LEFT  JOIN dbo.TPackage_tail TPT ON TAD.PID = TPT.AddStoragePid  AND TPT.TBStorage_PID=@pid 
                            LEFT JOIN dbo.TBase_Material TBM ON TAD.Mat_Code = TBM.Mat_Code
                            WHERE TAD.StockNo=@stockNo AND TAD.Sec_Mat_ID= @sec_Mat_ID ";
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@stockNo", DbType.String, stockNo);
            dbHelper.AddInParameter(cmd, "@sec_Mat_ID", DbType.String, sec_Mat_Id);
            dbHelper.AddInParameter(cmd, "@pid", DbType.String, pid);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 获取未处理入库单据列表
        /// </summary>
        /// <param name=""></param>
        /// <returns></returns>
        public DataTable DataListStorage()
        {
            string sqlStr = @"SELECT DISTINCT TBA.StockNo,TBA.CDT FROM TBill_AddStorage TBA 
                            LEFT JOIN TAddStorage_List TAL ON TAL.StockNo = TBA.StockNo
                            WHERE TBA.Bill_Status = 0  ORDER BY TBA.CDT DESC";
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 获取托盘在立体库中的信息
        /// </summary>
        /// <param name="trayId"></param>
        /// <returns></returns>
        public DataTable TrayCanPackage(string trayId)
        {

            string sqlStr = string.Format(@"SELECT TOP 1 Structure FROM TRelations TRP 
			                    LEFT JOIN TBase_Position TBP ON TRP.Pos_ID = TBP.Pos_ID 
			                    LEFT JOIN TBase_Zone TBZ ON TBZ.Zone_ID = TBP.Zone_ID 
			                    WHERE TRP.TRAY_ID = @TRAY_ID");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@TRAY_ID", DbType.String, trayId);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 物料编码是否存在物料表中
        /// </summary>
        /// <param name="matCode"></param>
        public bool MaterialCanPackage(string matCode)
        {
            string sqlStr = string.Format(@"select count(Mat_Code) from dbo.TBase_Material where Mat_Code = @Mat_Code");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@Mat_Code", DbType.String, matCode);
            object obj = dbHelper.ExecuteScalar(cmd);
            return int.Parse(obj.ToString()) > 0 ? true : false;
        }

        /// <summary>
        /// 执行组盘存储过程-绑定订单单号的
        /// </summary>
        /// <param name="strStockNo"></param>
        /// <param name="strMat_Code"></param>
        /// <param name="strTrayId"></param>
        /// <param name="SecMatId"></param>
        /// <param name="MatNum"></param>
        /// <param name="TypeOfSize"></param>
        /// <param name="TypeOfWeight"></param>
        /// <returns></returns>
        public StoreProceResult PackageCommon(string strStockNo, string strMat_Code, string strTrayId, long SecMatId, decimal MatNum, int TypeOfSize, int TypeOfWeight)
        {
            DbCommand cmd = dbHelper.GetStoredProcCommond("dbo.SP_PACKAGE_YMJX");
            dbHelper.AddInParameter(cmd, "@STOCKNO", DbType.String, strStockNo);
            dbHelper.AddInParameter(cmd, "@Mat_Code", DbType.String, strMat_Code);
            dbHelper.AddInParameter(cmd, "@TRAY_ID", DbType.String, strTrayId);
            dbHelper.AddInParameter(cmd, "@SEC_MAT_ID", DbType.Int64, SecMatId);
            dbHelper.AddInParameter(cmd, "@MAT_NUM", DbType.Decimal, MatNum);
            //dbHelper.AddInParameter(cmd, "@Act_Num", DbType.Decimal, Convert.ToDecimal(MatNum));
            dbHelper.AddInParameter(cmd, "@TypeOfSize", DbType.Int32, TypeOfSize);
            dbHelper.AddInParameter(cmd, "@TypeOfWeight", DbType.Int32, TypeOfWeight);
            dbHelper.AddInParameter(cmd, "@USERNAME", DbType.String, "RFClient");
            dbHelper.AddOutParameter(cmd, "@ResNO", DbType.Int32, 100);
            dbHelper.AddOutParameter(cmd, "@RESMsg", DbType.String, 255);
            dbHelper.ExecuteNonQuery(cmd);
            int resNo = Convert.ToInt32(dbHelper.GetParameter(cmd, "@RESNO").Value.ToString());
            string resMsg = dbHelper.GetParameter(cmd, "@RESMSG").Value.ToString();
            StoreProceResult res = new StoreProceResult()
            {
                ResNo = resNo,
                ResMsg = resMsg
            };
            return res;
        }

        /// <summary>
        /// 执行组盘存储过程-绑定订单单号的
        /// </summary>
        /// <param name="strStockNo"></param>
        /// <param name="strMat_Code"></param>
        /// <param name="strTrayId"></param>
        /// <param name="SecMatId"></param>
        /// <param name="MatNum"></param>
        /// <param name="TypeOfSize"></param>
        /// <param name="TypeOfWeight"></param>
        /// <returns></returns>
        public StoreProceResult ReplenPackageCommon(long taskId,string strStockNo, string strMat_Code, string strTrayId, long SecMatId, decimal MatNum, int TypeOfSize, int TypeOfWeight)
        {
            DbCommand cmd = dbHelper.GetStoredProcCommond("dbo.SP_PACKAGE_Replenish_YMJX");
            dbHelper.AddInParameter(cmd, "@TaskID", DbType.Int64, taskId);
            dbHelper.AddInParameter(cmd, "@STOCKNO", DbType.String, strStockNo);
            dbHelper.AddInParameter(cmd, "@Mat_Code", DbType.String, strMat_Code);
            dbHelper.AddInParameter(cmd, "@TRAY_ID", DbType.String, strTrayId);
            dbHelper.AddInParameter(cmd, "@SEC_MAT_ID", DbType.Int64, SecMatId);
            dbHelper.AddInParameter(cmd, "@MAT_NUM", DbType.Decimal,MatNum);
            //dbHelper.AddInParameter(cmd, "@Act_Num", DbType.Decimal, Convert.ToDecimal(MatNum));
            dbHelper.AddInParameter(cmd, "@TypeOfSize", DbType.Int32, TypeOfSize);
            dbHelper.AddInParameter(cmd, "@TypeOfWeight", DbType.Int32, TypeOfWeight);
            dbHelper.AddInParameter(cmd, "@USERNAME", DbType.String, "RFClient");
            dbHelper.AddOutParameter(cmd, "@ResNO", DbType.Int32, 100);
            dbHelper.AddOutParameter(cmd, "@RESMsg", DbType.String, 255);
            dbHelper.ExecuteNonQuery(cmd);
            int resNo = Convert.ToInt32(dbHelper.GetParameter(cmd, "@RESNO").Value.ToString());
            string resMsg = dbHelper.GetParameter(cmd, "@RESMSG").Value.ToString();
            StoreProceResult res = new StoreProceResult()
            {
                ResNo = resNo,
                ResMsg = resMsg
            };
            return res;
        }

        /// <summary>
        /// 执行组盘存储过程-特殊物料
        /// </summary>
        /// <param name="strMat_Code"></param>
        /// <param name="strTrayId"></param>
        /// <param name="SecMatId"></param>
        /// <param name="MatNum"></param>
        /// <param name="TypeOfSize"></param>
        /// <param name="TypeOfWeight"></param>
        /// <returns></returns>
        public StoreProceResult PackageCommonSpecial(string strMat_Code, string strTrayId, long SecMatId, decimal MatNum, int TypeOfSize, int TypeOfWeight)
        {
            DbCommand cmd = dbHelper.GetStoredProcCommond("dbo.SP_PACKAGE_YMJX_SPECIAL");
            dbHelper.AddInParameter(cmd, "@TRAY_ID", DbType.String, strTrayId);
            dbHelper.AddInParameter(cmd, "@SEC_MAT_ID", DbType.Int64, SecMatId);
            dbHelper.AddInParameter(cmd, "@MAT_NUM", DbType.Int32, MatNum);
            dbHelper.AddInParameter(cmd, "@TypeOfSize", DbType.Int32, TypeOfSize);
            dbHelper.AddInParameter(cmd, "@USERNAME", DbType.String, "RFClient");
            dbHelper.AddOutParameter(cmd, "@ResNO", DbType.Int32, 100);
            dbHelper.AddOutParameter(cmd, "@RESMsg", DbType.String, 255);
            dbHelper.ExecuteNonQuery(cmd);
            int resNo = Convert.ToInt32(dbHelper.GetParameter(cmd, "@RESNO").Value.ToString());
            string resMsg = dbHelper.GetParameter(cmd, "@RESMSG").Value.ToString();
            StoreProceResult res = new StoreProceResult()
            {
                ResNo = resNo,
                ResMsg = resMsg
            };
            return res;
        }

        /// <summary>
        /// 根据托盘和物料二级编码获取托盘容器编号
        /// </summary>
        /// <param name="strTrayId"></param>
        /// <param name="sec_Mat_Id"></param>
        /// <returns></returns>
        public string GetStorageKey(string strTrayId, long sec_Mat_Id)
        {
            string sqlStr = string.Format(@"select tbs.ContainerID from dbo.TBase_Storage tbs 
                  inner join dbo.TRel_TrayPosContainer tr on tbs.ContainerID=tr.ContainerID
                  inner join dbo.TBase_TrayPos ttp on ttp.Relations_ID=tr.Relations_ID
                  where ttp.Tray_ID=@Tray_ID AND tbs.Sec_Mat_ID=@Sec_Mat_ID");
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@Tray_ID", DbType.String, strTrayId);
            dbHelper.AddInParameter(cmd, "@Sec_Mat_ID", DbType.Int64, sec_Mat_Id);
            object obj = dbHelper.ExecuteScalar(cmd);
            return obj != null ? obj.ToString() : string.Empty;
        }

        /// <summary>
        /// 解盘
        /// </summary>
        /// <param name="StockNo"></param>
        /// <param name="ContainerID"></param>
        /// <param name="SecMatId"></param>
        /// <param name="MatNum"></param>
        /// <param name="strMessage"></param>
        /// <returns></returns>
        public StoreProceResult PackageDel(string StockNo, string ContainerID, long SecMatId, decimal MatNum)
        {
            DbCommand cmd = dbHelper.GetStoredProcCommond("dbo.SP_UNPACKAGE_YMJX");
            dbHelper.AddInParameter(cmd, "@StockNo", DbType.String, StockNo);
            dbHelper.AddInParameter(cmd, "@ContainerID", DbType.String, ContainerID);
            dbHelper.AddInParameter(cmd, "@SEC_MAT_ID", DbType.String, SecMatId.ToString());
            dbHelper.AddInParameter(cmd, "@MAT_NUM", DbType.Decimal, MatNum);
            dbHelper.AddInParameter(cmd, "@USERNAME", DbType.String, "RFClient");
            dbHelper.AddOutParameter(cmd, "@ResNO", DbType.Int32, 100);
            dbHelper.AddOutParameter(cmd, "@RESMsg", DbType.String, 255);
            dbHelper.ExecuteNonQuery(cmd);
            int resNo = Convert.ToInt32(dbHelper.GetParameter(cmd, "@RESNO").Value.ToString());
            string resMsg = dbHelper.GetParameter(cmd, "@RESMSG").Value.ToString();
            StoreProceResult res = new StoreProceResult()
            {
                ResNo = resNo,
                ResMsg = resMsg
            };
            return res;
        }

        /// <summary>
        /// 解盘-特殊物料
        /// </summary>
        /// <param name="ContainerID"></param>
        /// <param name="SecMatId"></param>
        /// <param name="MatNum"></param>
        /// <returns></returns>
        public StoreProceResult PackageDelSpecial(string ContainerID, long SecMatId, decimal MatNum)
        {
            DbCommand cmd = dbHelper.GetStoredProcCommond("dbo.SP_UNPACKAGE_YMJX_SPECIAL");
            dbHelper.AddInParameter(cmd, "@ContainerID", DbType.String, ContainerID);
            dbHelper.AddInParameter(cmd, "@SEC_MAT_ID", DbType.String, SecMatId.ToString());
            dbHelper.AddInParameter(cmd, "@MAT_NUM", DbType.Int32, MatNum);
            dbHelper.AddInParameter(cmd, "@USERNAME", DbType.String, "RFClient");
            dbHelper.AddOutParameter(cmd, "@ResNO", DbType.Int32, 100);
            dbHelper.AddOutParameter(cmd, "@RESMsg", DbType.String, 255);
            dbHelper.ExecuteNonQuery(cmd);
            int resNo = Convert.ToInt32(dbHelper.GetParameter(cmd, "@RESNO").Value.ToString());
            string resMsg = dbHelper.GetParameter(cmd, "@RESMSG").Value.ToString();
            StoreProceResult res = new StoreProceResult()
            {
                ResNo = resNo,
                ResMsg = resMsg
            };
            return res;
        }

        /// <summary>
        /// 根据托盘码获取盘点指令,物料等出库信息
        /// </summary>
        /// <param name="trayId"></param>
        /// <returns></returns>
        public DataTable GetCheckCommandMatInfoByTrayId(string trayId)
        {
            string strSql = @"SELECT TBS.PID, T.TaskID,T.Tray_ID,T.PosS_ID,T.PosE_ID,TSQ.WMSTypeName,TBM.Mat_Code,
                        TBS.Sec_Mat_ID,TBM.Mat_Name,TBI.TS01,TBS.Mat_Num,TBM.S05,TBM.S01,TBM.TM01,TBM.TM03,TBM.S02
                        FROM dbo.TCommands T LEFT JOIN dbo.TCommandsDetail TD ON T.TaskID = TD.TaskID 
                        LEFT JOIN dbo.TSQModeRelations TSQ ON T.TaskMode = TSQ.WMSType
                        LEFT JOIN dbo.TBase_TrayPos TB ON T.Tray_ID = TB.Tray_ID
                        LEFT JOIN dbo.TRel_TrayPosContainer TRP ON TB.Relations_ID = TRP.Relations_ID
                        LEFT JOIN dbo.TBase_Storage TBS ON TRP.ContainerID = TBS.ContainerID
                        LEFT JOIN dbo.TBase_Interface TBI ON TBS.Sec_Mat_ID = TBI.Sec_Mat_ID
                        LEFT JOIN dbo.TBase_Material TBM ON TBI.Mat_Code = TBM.Mat_Code
                        WHERE T.Tray_ID=@trayId";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSql);
            dbHelper.AddInParameter(cmd, "@trayId", DbType.String, trayId);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 根据托盘码获取补货指令，物料等出库信息
        /// </summary>
        /// <param name="trayId"></param>
        /// <returns></returns>
        public DataTable GetReplenCommandMatInfoByTrayId(string trayId)
        {
            string strSql = @"SELECT TD.DETAIL_PID PID,T.TaskID,T.Tray_ID,T.PosS_ID,T.PosE_ID,T.TaskMode,TSR.WMSTypeName,TSL.StockNo,
                            TSL.Mat_Code,TSL.Num,TD.MAT_NUM,TSL.Act_Num,TSL.Sec_Mat_ID,TBM.Mat_Name,TSL.S05,TSL.TS01
							FROM dbo.TCommands T LEFT JOIN dbo.TCommandsDetail TD
                            ON T.TaskID = TD.TaskID LEFT JOIN dbo.TAddStorage_List TSL
                            ON TD.DETAIL_PID = TSL.PID LEFT JOIN dbo.TSQModeRelations TSR
                            ON T.TaskMode = TSR.WMSType LEFT JOIN dbo.TBase_Interface TBI
                            ON TSL.Sec_Mat_ID = TBI.Sec_Mat_ID  LEFT JOIN dbo.TBase_Material TBM
                            ON TBI.Mat_Code = TBM.Mat_Code
                            WHERE T.Tray_ID=@trayId";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSql);
            dbHelper.AddInParameter(cmd, "@trayId", DbType.String, trayId);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

        /// <summary>
        /// 根据托盘码获取拣选指令，物料等出库信息
        /// </summary>
        /// <param name="trayId"></param>
        /// <returns></returns>
        public DataTable GetChooseCommandMatInfoByTrayId(string trayId)
        {
            string strSql = @"SELECT TD.DETAIL_PID PID,T.TaskID,T.Tray_ID,T.PosS_ID,T.PosE_ID,T.TaskMode,TSR.WMSTypeName,TSL.StockNo,
                            TSL.Mat_Code,TSL.Num,TSL.Act_Num,TD.MAT_NUM,TBM.Mat_Name,TBM.S01,TBM.S02,TBM.TM01,TBM.TM03,TSL.TS01,TBM.Unit_Base
                            FROM dbo.TCommands T LEFT JOIN dbo.TCommandsDetail TD
                            ON T.TaskID = TD.TaskID LEFT JOIN dbo.TDelStorage_List TSL
                            ON TD.DETAIL_PID = TSL.PID LEFT JOIN dbo.TSQModeRelations TSR
                            ON T.TaskMode = TSR.WMSType LEFT JOIN dbo.TBase_Material TBM
                            ON TSL.Mat_Code = TBM.Mat_Code
                            WHERE T.Tray_ID=@trayId";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSql);
            dbHelper.AddInParameter(cmd, "@trayId", DbType.String, trayId);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

        public decimal GetTrayMatNumBySectId(string trayId,long secMatId)
        {
            string strSql = @"SELECT Mat_Num FROM dbo.TBase_Storage TBS INNER JOIN dbo.TRel_TrayPosContainer TTP
							ON TBS.ContainerID = TTP.ContainerID INNER JOIN dbo.TBase_TrayPos TBT 
							ON TTP.Relations_ID = TBT.Relations_ID 
							WHERE TBS.Sec_Mat_ID=@Sec_Mat_ID AND TBT.Tray_ID=@trayId";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSql);
            dbHelper.AddInParameter(cmd, "@Sec_Mat_ID", DbType.Int64, secMatId);
            dbHelper.AddInParameter(cmd, "@trayId", DbType.String, trayId);
            object obj = dbHelper.ExecuteScalar(cmd);
            if (obj != null)
                return decimal.Parse(obj.ToString());
            else
                return 0;
        }

        public int GetRelepenCmdRecordByTrayId(string trayId)
        {
            string sqlStr = @"SELECT COUNT(PID) FROM dbo.TReplenCommandRecord WHERE TrayID=@trayId";
            DbCommand cmd = dbHelper.GetSqlStringCommond(sqlStr);
            dbHelper.AddInParameter(cmd, "@trayId", DbType.String, trayId);
            object obj = dbHelper.ExecuteScalar(cmd);
            if(obj != null)
                return int.Parse(obj.ToString());
            else
                return 0;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="trayId"></param>
        /// <returns></returns>
        public DataTable GetReplenCommandRecordByTrayId(string trayId)
        {
            string strSql = @"SELECT T.DETAIL_PID PID,T.TaskID,T.TrayID,T.TaskMode,TSL.StockNo,TBM.S01,TBM.TM01,TBM.S02,TBM.TM03,
                            TSL.Mat_Code,TSL.Num,T.MAT_NUM,TSL.Act_Num,TSL.Sec_Mat_ID,TBM.Mat_Name,TSL.S05,TSL.TS01
							FROM dbo.TReplenCommandRecord T  LEFT JOIN dbo.TAddStorage_List TSL
                            ON T.DETAIL_PID = TSL.PID  LEFT JOIN dbo.TBase_Interface TBI
                            ON TSL.Sec_Mat_ID = TBI.Sec_Mat_ID  LEFT JOIN dbo.TBase_Material TBM
                            ON TBI.Mat_Code = TBM.Mat_Code
                            WHERE T.TrayID=@trayId";
            DbCommand cmd = dbHelper.GetSqlStringCommond(strSql);
            dbHelper.AddInParameter(cmd, "@trayId", DbType.String, trayId);
            DataTable dt = dbHelper.ExecuteDataTable(cmd);
            return dt;
        }

       
        public StoreProceResult ReplenStorageOverWeight(long taskId, string strStockNo, string strTrayId, long SecMatId, decimal MatNum)
        {
            DbCommand cmd = dbHelper.GetStoredProcCommond("dbo.SP_Replenish_OverWeight_YMJX");
            dbHelper.AddInParameter(cmd, "@TRAY_ID", DbType.String, strTrayId);
            dbHelper.AddInParameter(cmd, "@TaskID", DbType.Int64, taskId);
            dbHelper.AddInParameter(cmd, "@SEC_MAT_ID", DbType.Int64, SecMatId);
            dbHelper.AddInParameter(cmd, "@MAT_NUM", DbType.Decimal, MatNum);
            dbHelper.AddInParameter(cmd, "@STOCKNO", DbType.String, strStockNo);
            dbHelper.AddOutParameter(cmd, "@ResNO", DbType.Int32, 100);
            dbHelper.AddOutParameter(cmd, "@RESMsg", DbType.String, 255);
            dbHelper.ExecuteNonQuery(cmd);
            int resNo = Convert.ToInt32(dbHelper.GetParameter(cmd, "@RESNO").Value.ToString());
            string resMsg = dbHelper.GetParameter(cmd, "@RESMSG").Value.ToString();
            StoreProceResult res = new StoreProceResult()
            {
                ResNo = resNo,
                ResMsg = resMsg
            };
            return res;
        }

    }
}
